#!/usr/bin/perl -w
use strict;

use FindBin;
use lib ("$FindBin::Bin/..", "/net/cpp-group/Leo/bin");
use ortho_species;
use Spreadsheet::WriteExcel::Big;
use dbh_helper;
use Data::Dump qw(dump);

use DBI;


print STDERR <<"HEADLINE";
888888888888888888888888888888888888888888888888888888888888888888888

    xls_analyse_protocols_venn

	Description:
		Puts the venn diagram relationships for ensembl, ensp and 
		inparanoid into an Excel graph

888888888888888888888888888888888888888888888888888888888888888888888

HEADLINE





#-----------------------------------------------------------------------------------------

#	get_3_way_venn_names

#		pass in a hash with 3 categories and associated values
#		Get venn names (joined by 'and') and sums of the associated values
#		in the overlapping venn categories

#-----------------------------------------------------------------------------------------
sub get_3_way_venn_names(\%\%)
{
	my ($categories,  $venn_names) = @_;
	%$venn_names = %$categories;
	for my $p1(keys %$categories)
	{
		for my $p2(keys %$categories)
		{
			next unless $p1 lt $p2;
			$venn_names->{"$p1 and $p2"} = $categories->{$p1} + $categories->{$p2};
		}
	}

	# total
	my $total_val = 0;
	$total_val += $_ for(values %$categories);
	$venn_names->{join (" and ", sort keys %$categories)} = $total_val;
	return $total_val;
}

#-----------------------------------------------------------------------------------------

#	print_protocol_venn_diagram

#		Print in the counts for the different parts of the venn diagram for
#		protocol prediction numbers. These may be all relationships or just
#		orthologs and paralogs

#-----------------------------------------------------------------------------------------
sub print_protocol_venn_diagram($$$$$$\%\%$$$)
{
	my ($dbh, $workbook, $blue_format, $black_format, $percent_format,
		$total_id_filter, $protocol_combinations, $protocol_order, 
		$protocol_id_name, $sql_text, $description) = @_;
	my $total_count = dbh_get_single_value($dbh,
										   "SELECT COUNT (*) FROM ".
										   "orthologs.pairwise_overlap WHERE ".
										   "$protocol_id_name & $total_id_filter <> 0 ".
										   "$sql_text");

	my $worksheet = $workbook->addworksheet($description);
	$worksheet->set_column(0, 0,  6);
	$worksheet->set_column(1, 1,  11);
	$worksheet->set_column(2, 2,  20);
	
	my @protocol_combinations = sort {$protocol_order->{$a} <=> $protocol_order->{$b}} 
								keys %$protocol_order;

	
	my $row_cnt = 0;
	$worksheet->write_row($row_cnt++, 0, ["count", "percentage", "category"], $blue_format);
	
	$worksheet->write_row($row_cnt++, 0, [$total_count, '', 'total']);
	for my $name(@protocol_combinations)
	{
		my $protocol_id = $protocol_combinations->{$name};
		my $sql_cmd = <<"PL/SQLCMD";
		SELECT 
			COUNT (*) 
		FROM 
			orthologs.pairwise_overlap 
		WHERE 
			$protocol_id_name & $total_id_filter = $protocol_id
            $sql_text
PL/SQLCMD
#        print STDERR $sql_cmd;
		my $count = dbh_get_single_value($dbh, $sql_cmd);
        $worksheet->write_row($row_cnt, 0, [$count, '', $name]);
		$worksheet->write($row_cnt++,	1, $count / $total_count, $percent_format);
	}
}
	

	
#-----------------------------------------------------------------------------------------
#	Get DB parameters
#
use db_parameters;
use ortho_species;
#my @db_param = get_db_parameters();
#
#	Get DB parameters
#-----------------------------------------------------------------------------------------






#
# connect to genome database
#
my $dbh = connect_to_panda();

	#
	# get protocol_ids
	#
	my %protocol_names_of_interest;
	@protocol_names_of_interest{qw(canis_homo ensembl inparanoid)} = (1,2,4);
	my %protocol_names;
	for my $protocol (get_orthologs_protocols())
	{
		my ($protocol_name,  $protocol_id) = @$protocol;
		$protocol_names{$protocol_name} = $protocol_id 
			if (exists $protocol_names_of_interest{$protocol_name})
	};
	my %protocol_combinations;
	my $total_ids = get_3_way_venn_names(%protocol_names, %protocol_combinations);
	my %protocol_order;
	get_3_way_venn_names(%protocol_names_of_interest, %protocol_order);







	system ("mkdir -p $dir_orthologs_output/excel") and die;

	#
	#	workbook
	#
	my $workbook  = Spreadsheet::WriteExcel::Big->new("$dir_orthologs_output/excel/protocols_venn.xls");
	die "Problems creating new Excel file: $!" unless defined $workbook;


	#
	#	formats
	#
	# blue
	my $boldblue_format = $workbook->addformat();
	$boldblue_format->set_bold();
	$boldblue_format->set_color('blue');

	# red
	my $boldred_format = $workbook->addformat();
	$boldred_format->set_bold();
	$boldred_format->set_color('red');

	# standard
	my $black_format = $workbook->addformat();

	# standard
	my $percent_format = $workbook->addformat();
	$percent_format->set_num_format("0.0%");



	#
	#	Venn diagram for relationship
	#
	print_protocol_venn_diagram($dbh, $workbook, $boldblue_format, $black_format,
								$percent_format,
								$total_ids, %protocol_combinations, 
								%protocol_order,
								'protocol_id', '', 'relationships');

	#
	#	Venn diagram for orthologs
	#
	print_protocol_venn_diagram($dbh, $workbook, $boldblue_format, $black_format,
								$percent_format,
								$total_ids, %protocol_combinations, 
								%protocol_order,
								'protocol_id', 'AND species1 <> species2', 
								'all orthologs');

	#
	#	Venn diagram for orthologs
	#
	print_protocol_venn_diagram($dbh, $workbook, $boldblue_format, $black_format,
								$percent_format,
								$total_ids, %protocol_combinations, 
								%protocol_order, 
								'protocol_id_1s', 'AND species1 <> species2', 
								'1 to 1 orthologs');

	#
	#	Venn diagram for orthologs
	#
	print_protocol_venn_diagram($dbh, $workbook, $boldblue_format, $black_format,
								$percent_format,
								$total_ids, %protocol_combinations, 
								%protocol_order, 
								'protocol_id_manys', 'AND species1 <> species2', 
								'manys orthologs');
	

	#
	#	Venn diagram for all paralogs
	#
	print_protocol_venn_diagram($dbh, $workbook, $boldblue_format, $black_format,
								$percent_format,
								$total_ids, %protocol_combinations, 
								%protocol_order, 
								'protocol_id', 'AND species1 = species2', 
								'all paralogs');
	#
	#	Venn diagram for dog paralogs
	#
	print_protocol_venn_diagram($dbh, $workbook, $boldblue_format, $black_format,
								$percent_format,
								$total_ids, %protocol_combinations, 
								%protocol_order, 
								'protocol_id', 'AND species1 = species2 and '.
								'species1 = \'canis\' and species2 = \'canis\'', 
								'dog paralogs');

	#
	#	Venn diagram for human paralogs
	#
	print_protocol_venn_diagram($dbh, $workbook, $boldblue_format, $black_format,
								$percent_format,
								$total_ids, %protocol_combinations, 
								%protocol_order, 
								'protocol_id', 'AND species1 = species2 and '.
								'species1 = \'homo\' and species2 = \'homo\'', 
								'human paralogs');

	$workbook->close();




print STDERR "\tCompleted\n";



$dbh->disconnect();





